﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace QuanLyThietBi
{
    class CXuly_loaiphong
    {
        SqlConnection con;
        public CXuly_loaiphong(SqlConnection con)
        {
            this.con = con;
        }
        public List<CLoaiphong> getdsloaiphong()
        {
            List<CLoaiphong> list = new List<CLoaiphong>();
            con.Open();
            string strCom = "select * from loaiphong";
            SqlCommand com = new SqlCommand(strCom, con);
            SqlDataReader data = com.ExecuteReader();
            while (data.Read())
            {
                CLoaiphong l = new CLoaiphong();
                l.Maloaiphong = data["maloaiphong"].ToString();
                l.Tenloaiphong = data["tenloaiphong"].ToString();
               
                list.Add(l);

            }
            con.Close();
            foreach (CLoaiphong x in list)
            {
                x.Chitietloaiphong = getdschitietloaiphong(x.Maloaiphong);
            }
            return list;
        }
        public List<CChitietloaiphong> getdschitietloaiphong(string maloaiphong)
        {
            List<CChitietloaiphong> l = new List<CChitietloaiphong>();
            con.Open();
            string str = "select * from chitietloaiphong where maloaiphong=@maloaiphong";
            SqlCommand com = new SqlCommand(str, con);
            com.Parameters.AddWithValue("@maloaiphong", maloaiphong);
            SqlDataReader data = com.ExecuteReader();
            while (data.Read())
            {
                CChitietloaiphong ct = new CChitietloaiphong();
                ct.Maloaiphong = data["maloaiphong"].ToString();
                ct.Loaivattu = new CLoaivattu();
                ct.Loaivattu.Maloai = data["maloaivattu"].ToString();
                int soluong;
                int.TryParse(data["soluong"].ToString(),out soluong);
                ct.Soluong = soluong;
                l.Add(ct);
            }
            con.Close();
            return l;

        }
        public CLoaiphong tim(string a)
        {
            con.Open();
            string str = "select * from loaiphong where maloaiphong=@a OR tenloaiphong=@a";
            SqlCommand com = new SqlCommand(str, con);
            com.Parameters.AddWithValue("@a", a);
            SqlDataReader data = com.ExecuteReader();
            CLoaiphong x = null;
            while (data.Read())
            {
                x = new CLoaiphong();
                x.Maloaiphong = data["maloaiphong"].ToString();
                x.Tenloaiphong = data["tenloaiphong"].ToString();

            }
            con.Close();
            if (x == null) return x;
            x.Chitietloaiphong = getdschitietloaiphong(x.Maloaiphong);
            return x;
        }
        public bool them(CLoaiphong x)
        {
            if (tim(x.Maloaiphong) != null) return false;
            con.Open();
            string str = "insert into loaiphong values(@maloaiphong,@tenloaiphong)";
            SqlCommand com = new SqlCommand(str, con);
            com.Parameters.AddWithValue("@maloaiphong", x.Maloaiphong);
            com.Parameters.AddWithValue("@tenloaiphong", x.Tenloaiphong);
            com.ExecuteNonQuery();
            con.Close();
            foreach (CChitietloaiphong y in x.Chitietloaiphong)
            {
                themchitietloaiphong(y);
            }
            return true;
        }
        public void themchitietloaiphong(CChitietloaiphong x)
        {
            con.Open();
            string str = "insert into chitietloaiphong values(@maloaiphong,@maloaivattu,@soluong)";
            SqlCommand com = new SqlCommand(str, con);
            com.Parameters.AddWithValue("@maloaiphong", x.Maloaiphong);
            com.Parameters.AddWithValue("@maloaivattu", x.Maloaivattu);
            com.Parameters.AddWithValue("@soluong", x.Soluong);
            com.ExecuteNonQuery();
            con.Close();
        }
        public void sua(CLoaiphong p)
        {

            con.Open();
            string str = "update loaiphong set tenloaiphong=@tenloaiphong where maloaiphong=@maloaiphong";
            SqlCommand com = new SqlCommand(str, con);
            com.Parameters.AddWithValue("@maloaiphong", p.Maloaiphong);
            com.Parameters.AddWithValue("@tenloaiphong", p.Tenloaiphong);
            com.ExecuteNonQuery();
            con.Close();
            xoachitietloaiphong(p.Maloaiphong);
            foreach (CChitietloaiphong x in p.Chitietloaiphong)
            {
                suachitietloaiphong(x);
            }
        }
        public void suachitietloaiphong(CChitietloaiphong x)
        {
            themchitietloaiphong(x);
        }
        public void xoa(string maloaiphong)
        {
            List<CChitietloaiphong> lst = getdschitietloaiphong(maloaiphong);
            xoachitietloaiphong(maloaiphong);
            bool flag = true;
            con.Open();
            string str = "delete from loaiphong where maloaiphong=@maloaiphong";
            SqlCommand com = new SqlCommand(str, con);
            com.Parameters.AddWithValue("@maloaiphong", maloaiphong);
            try
            {
                com.ExecuteNonQuery();
            }
            catch (SqlException)
            {
                MessageBox.Show("Lỗi khoá ngoại");
                con.Close();
                flag = false;
            }
            con.Close();
            if (flag==false)
            {
                foreach(CChitietloaiphong x in lst)
                    themchitietloaiphong(x);
            }
        }
        public void xoachitietloaiphong(string maloaiphong)
        {
            con.Open();
            string str = "delete from chitietloaiphong where maloaiphong=@maloaiphong";
            SqlCommand com = new SqlCommand(str, con);
            com.Parameters.AddWithValue("@maloaiphong", maloaiphong);
            com.ExecuteNonQuery();
            con.Close();
        }
    }
}
